clear all
set more off
*set matsize 800
cd "/Users/carlyurban/Dropbox/SSI_WelfareShift"
version 11.0
*NOTE: Panel created in Code/PHA_to_county_KYLE
use "Data/kyle/CNTY_panel_wStatePHA.dta", clear


/* NOTE THESE DATA ARE RESTRICTED AND NEED SPECIAL SSA APPLICATION TO ACCESS*/
*Import SSI data
merge 1:1 cntyfips year using "SSA DATA", gen(merge_ssi)
	rename APPS_18_TO_64 ssi_apps
	rename AWARDS_18_TO_64 ssi_awards
keep if merge_ssi==3

merge 1:1 cntyfips year using "SSA DATA", gen(merge_ssi2)
	gen ssi_apps_old_young = APPS_ALL - ssi_apps
	gen ssi_awards_old_young = AWARDS_ALL - ssi_awards
	gen ln_ssi_apps_old_young = ln(ssi_apps_old_young)
	gen ln_ssi_awards_old_young = ln(ssi_awards_old_young)
	label var ssi_apps_old_young "SSI Apps for ALL minus SSI Apps for Prime Aged == SSI Apps old / young"
	label var ln_ssi_apps_old_young "\shortstack{ln(SSI Apps) \\ Non-Prime Aged}"
	label var ln_ssi_awards_old_young "\shortstack{ln(SSI Awards) \\ Non-Prime Aged}"
keep if merge_ssi2 == 3
*This will drop all HAWAII COUNTIES // we dont have SSI data on Hawaii.

destring cntyfips, gen(fips)

*Make Open and Disability Pref Variable.
gen open_disab = (max_disability_pref == 1 & max_everopen == 1) 
label var open_disab "Open X DP" 

/******************
*Merge in AUX Data.
*******************/

*Number of Public Housing Units in County/Year
	merge 1:1 cntyfips year using "AUXdata/CNTY_pct_disabled_lt62_SSIWelfareAssistance_data.dta", keepusing(num_PubH_units_CNTY months_waiting)
		drop if _merge == 2 
		drop _merge
		replace num_PubH_units_CNTY = 0 if num_PubH_units == .
		
		
*Merge in FHFA COUNTY HPI
	*NOTE: Downloaded from here: https://www.fhfa.gov/DataTools/Downloads/Pages/House-Price-Index-Datasets.aspx
	*                            "Counties (Developmental Index; Not Seasonally Adjusted).xlsx"
/*
preserve
	
	import excel "Data\FHFA_CNTY_HPI.xlsx", clear sheet("county") cellrange(A7:H88496) firstrow
		destring Year, replace
		rename Year year
		rename FIPScode cntyfips
		keep if inrange(year, 2010, 2017)
		keep cntyfips year HPI 
	save "Data/FHFA_CNTY_HPI_2010_17.dta", replace
	
restore
*/
	merge 1:1 cntyfips year using "AUXdata/FHFA_CNTY_HPI_2010_17.dta"
		keep if _merge ==1 | _merge == 3
		destring HPI, replace

		
** Bring in State SSI Supplements
gen Year = year
destring statefips,gen(FIPS)
merge m:1 FIPS 	Year using "AUXdata/SSI_Supp_new.dta",keep(master match) nogen keepusing(ssisupp)



*Merge in KPRC_Benefits data
/*
preserve
import excel "Data\UKCPR_National_Welfare_Data_Update_052120.xlsx", sheet("Data") firstrow clear
	*Get max between state and federal unemployment 
		egen max_min_wage = rowmax(FederalMinimumWage StateMinimumWage)
		
	*Get Maximum State EITC (2kids) : EH: No this is federal
		*gen max_EITC = EITCMaximumCredit2Dependents
		
	*Get AFDC/TANF maximum (3 person fam)
		gen max_AFDCTANF = AFDCTANFBenefitfor3personf
		
	*Get SNAP Benefits (3 person fam) EH: SNAP bens are federal (except HI/AK which we don't have SSI data for)
		*gen max_SNAP = FSSNAPBenefitfor3personfam
	save "Data/KPRC_Benefits_18.dta", replace
restore
*/
	rename State state_name
	merge m:1 state_name year using "AUXdata/KPRC_Benefits_18.dta",  keepusing(max_min_wage StateEITCRate max_AFDCTANF state_name) keep(master match) nogen
	
*** TANF Time limit data
merge m:1 state_name Year using "AUXdata/TANF_TimeLimit.dta",keep(master match) nogen keepusing(tl_lamos)
drop Year FIPS	
*Merge in BLS County/Year Unemployment Rates
/*
preserve 
	import excel "Data/county_u_rate_2010_2013.xlsx", clear sheet ("BLS Data Series") firstrow
	foreach var of varlist Jan2010 - Dec2013 {
		rename `var' date_`var'
	}
	drop SeriesID statefip
	reshape long date_, i(countyfip) j(year) string
	rename date_ cnty_unemp
	*remove months
	replace year = regexr(year, "[A-Za-z]+", "")
	destring year, replace
	collapse (mean) cnty_unemp, by(countyfip year)
	rename countyfip cntyfips 
	destring cntyfips, replace
	save "Data/county_u_rate_yearly_2010_13.dta", replace
	
	import excel "Data/county_u_rate_2014_2017.xlsx", clear sheet ("BLS Data Series") firstrow
	foreach var of varlist Jan2014 - Dec2017 {
		rename `var' date_`var'
	}
	drop SeriesID statefip
	reshape long date_, i(countyfip) j(year) string
	rename date_ cnty_unemp
	*remove months
	replace year = regexr(year, "[A-Za-z]+", "")
	destring year, replace
	collapse (mean) cnty_unemp, by(countyfip year)
	rename countyfip cntyfips 
	destring cntyfips, replace
	save "Data/county_u_rate_yearly_2014_17.dta", replace

	restore 
*/

*Merge in BLS Data Created Above.
	destring cntyfips, replace
	merge 1:1 cntyfips year using "AUXdata/county_u_rate_yearly_2010_13.dta", keep(master match) nogen
	merge 1:1 cntyfips year using "AUXdata/county_u_rate_yearly_2014_17.dta", keep(master match_up match_confl) nogen update
		label var cnty_unemp "County Level Unemployment from BLS LAUS" 



*Make SSA Office Region Variables
	gen ssi_office = "Atlanta" if inlist(state_name, "AL", "FL", "GA", "KY", "MS", "NC", "SC", "TN")
	replace ssi_office = "Boston" if inlist(state_name, "CT", "ME", "MA", "NH", "RI", "VT")
	replace ssi_office = "Chicago" if inlist(state_name, "IL", "IN", "MI", "MN", "OH", "WI")
	replace ssi_office = "Dallas" if inlist(state_name, "AR", "LA", "NM", "OK", "TX")
	replace ssi_office = "Denver" if inlist(state_name, "CO", "MT", "ND", "SD", "UT", "WY")
	replace ssi_office = "Kansas City" if inlist(state_name, "IA", "KS", "MO", "NE")
	replace ssi_office = "New York" if inlist(state_name, "NJ", "NY", "PR", "VI")
	replace ssi_office = "Philiadelphia" if inlist(state_name, "DE", "MD", "PA", "VA", "WV")
	replace ssi_office = "San Fran" if inlist(state_name, "AS", "AZ", "CA", "GU", "HI", "NV")
	replace ssi_office = "Seattle" if inlist(state_name, "AK", "ID", "OR", "WA")
	*Make numeric variable for office regions.
	encode ssi_office, gen(ssi_office_code)

	
	
	
	
*ANALYSIS

*Make Analysis Variables
	foreach var of varlist pop_white - pop_age_65p {
		gen pct_`var'=`var'/pop
	}

*Log Apps and Awards
	gen ln_ssi_apps = ln(ssi_apps) 
	gen ln_ssi_awards = ln(ssi_awards)

*Make countyfips variable
	destring statefips, replace
	drop countyfips
	rename fips countyfips

*XTset data so we can include LAGS easily in the Regs
	xtset countyfips year



*Make a list of county-level controls to add
# delimit ;
	local cnty_cntrl "
							pct_pop_age_18_64
							num_PubH_units_CNTY
							HPI
							cnty_unemp
						"
;
# delimit cr

*Make a list of county-level controls to add
# delimit ;
	local cnty_cntrl_noPCT1864 "
							num_PubH_units_CNTY
							HPI
							cnty_unemp
						"
;
# delimit cr

*Make a list of state level controls to add
# delimit ;
	local state_cntrl "
							max_min_wage
							StateEITCRate
							max_AFDCTANF
							ssisupp
							tl_lamos
						 "
;
# delimit cr

*Make a list of state level controls to add (not including TANF for the TANF regs)
# delimit ;
	local state_cntrl_noTANF "
							max_min_wage
							StateEITCRate
							ssisupp
							tl_lamos
						 "
;
# delimit cr


*new variables and labeling for esttab
gen pop_age_18_64_tt = pop_age_18_64 / 10000
gen awards_per_cap = ssi_awards / pop_age_18_64_tt
gen apps_per_cap = ssi_apps / pop_age_18_64_tt


label var ssi_apps "SSI Apps"
label var ln_ssi_apps "ln(SSI Apps)"
label var apps_per_cap "\shortstack{SSI Apps \\ Per Cap.} 

label var ssi_awards "SSI Awards"
label var ln_ssi_awards "ln(SSI Awards)"
label var awards_per_cap "\shortstack{SSI Awards \\ Per Cap.}"


label var months_open "Months"

label var months_wgt_avg "\shortstack{Months x Disability Pref}" 

replace months_open = 0 if months_open == . 
 
/* SUMMARY STATS */



local x1 "num_pha max_everopen PHA_always_closed PHA_always_open months_open max_disability_pref months_wgt_avg ssi_awards awards_per_cap ssi_apps apps_per_cap "
su  `x1' 
des `x1'   

estpost tabstat `x1' if HPI!=.,  statistics(mean sd count) columns(statistics)
eststo b1
esttab b1 using "Output/X_chars_county.tex", cells("mean(fmt(3))" "sd(fmt(3)par)"  "count(fmt(0))")  noobs nonumbers unstack label legend replace 	


/*
2. Analysis
*/



// Table 4 - Main Specification


{
*Column 1 -- 
xtset countyfips

areg apps_per_cap  months_wgt_avg months_open `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year [aweight=pop],  a(countyfips) robust
eststo t1_col1

*Column 2 -- 
xtset countyfips year
areg apps_per_cap  months_wgt_avg  months_open L.months_wgt_avg L.months_open `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year [aweight=pop],  robust a(countyfips)
eststo t1_col2

*Column 3 --  
areg awards_per_cap  months_wgt_avg months_open `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year i.year [aweight=pop],  a(countyfips)  robust
eststo t1_col3

*Column 4 -- 
xtset countyfips year
areg awards_per_cap  months_wgt_avg  months_open L.months_wgt_avg L.months_open  `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year [aweight=pop],   robust a(countyfips)
eststo t1_col4


# delimit ;
	esttab t1_col1 t1_col2 t1_col3 t1_col4 
	using "Output/table1_wStatePHA_percap.tex"
	,
		keep(months_wgt_avg L.months_wgt_avg months_open L.months_open)
		indicate(
				 "County and Year FE = 2012.year "
				 "State Policy Variables = max_min_wage"
				 "County Controls = HPI"
				 "Region-by-Year FE = 10.ssi_office_code#2014.year"
				 )
		varlabels(months_wgt_avg "\shortstack{Months Open x Disability Pref}"
				  L.months_wgt_avg "\shortstack{Lag Months Open x Disability Pref}"
				  months_open "\shortstack{Months Open}"
				  L.months_open "\shortstack{Lag Months Open}")
		b(%05.4fc)
		se(%05.4fc)
		r2
		replace
		label
		eqlabels(none)
		star(* 0.10 ** 0.05 *** 0.01)
		stats(N dv_mean  , label("N" "DV Mean"   ) fmt(%6.0fc %5.3f))

;
# delimit cr
}

*Table A.3 Panel C
* only counties with  disability preference. Months open as independent variable. 
preserve
	*keep if a county never had a disability preference in the period. 
	bysort countyfips: egen max_disab = max(max_disability_pref)
	keep if max_disab == 1

*Column 1 -- 
xtset countyfips
areg apps_per_cap   months_wgt_avg `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year [aweight=pop], a(countyfips) robust
eststo t1_col1

*Column 2 -- 
xtset countyfips 
areg awards_per_cap   months_wgt_avg  `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year i.year [aweight=pop],  a(countyfips) robust
eststo t1_col2




# delimit ;
	esttab t1_col1 t1_col2  
	using "Output/table2b_wStatePHA_alldispref_percap.tex"
	,
		keep(months_wgt_avg L.months_wgt_avg)
		indicate(
				 "County and Year FE = 2012.year "
				 "State Policy Variables = max_min_wage"
				 "County Controls = HPI"
				 "Region-by-Year FE = 10.ssi_office_code#2014.year"
				 )
		varlabels(months_wgt_avg "\shortstack{Months Open x Disability Pref}"
				  L.months_wgt_avg "\shortstack{Lag Months Open x Disability Pref}")
		b(%05.4fc)
		se(%05.4fc)
		r2
		replace
		label
		eqlabels(none)
		star(* 0.10 ** 0.05 *** 0.01)
		stats(N  dv_mean , label("N"    "DV Mean" ) fmt(%6.0fc %5.3f))

;
# delimit cr

restore


*Table A.3 -- Panel B (Drop data where we dont have full county coverage_


preserve
rename countyfips cntyfips
merge 1:1 cntyfips year using "AUXdata/pha_full_number.dta", gen(pha_num_mrg)
	*Keep if the number of PHA we have data on is within 1 of the HUD data
	keep if num_pha == n_pha_full_file | num_pha == n_pha_full_file - 1 | num_pha == n_pha_full_file + 1 // benefit of the doubt if were 1 pha off
rename cntyfips countyfips
sort countyfips year
***TABLE 3-PanelB, Main effect***
*Column 1 -- 
xtset countyfips
areg apps_per_cap  months_wgt_avg months_open `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year [aweight=pop],  a(countyfips) robust
eststo t1_col1


*Column 2 --  
areg awards_per_cap  months_wgt_avg months_open `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year i.year [aweight=pop],  a(countyfips)  robust
eststo t1_col2

 

# delimit ;
	esttab t1_col1 t1_col2  
	using "Output/table3_panelB_wStatePHA.tex"
	,
		keep(months_wgt_avg L.months_wgt_avg months_open L.months_open)
		b(%05.4fc)
		se(%05.4fc)
		varlabels(months_wgt_avg "\shortstack{Months Open x Disability Pref}"
				  L.months_wgt_avg "\shortstack{Lag Months Open x Disability Pref}"
				  months_open "\shortstack{Months Open}"
				  L.months_open "\shortstack{Lag Months Open}")
		nonotes
		nonumbers
		r2
		replace
		label
		eqlabels(none)
		star(* 0.10 ** 0.05 *** 0.01)
		stats(N dv_mean , label("N"    "DV Mean" ) fmt(%6.0fc %5.3f))

;
# delimit cr

restore

// Table A.3 Panel D- no pop weight 

{
*Column 1 -- 

areg apps_per_cap  months_wgt_avg months_open `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year ,  a(countyfips) robust
eststo t1_col1

*Column 2 -- 

areg awards_per_cap  months_wgt_avg months_open `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year i.year ,  a(countyfips)  robust
eststo t1_col2



# delimit ;
	esttab t1_col1 t1_col2  
	using "Output/table5_wStatePHA_popwgt.tex"
	,
		keep(months_wgt_avg L.months_wgt_avg months_open L.months_open)
		indicate(
				 "County and Year FE = 2012.year "
				 "State Policy Variables = max_min_wage"
				 "County Controls = HPI"
				 "Region-by-Year FE = 10.ssi_office_code#2014.year"
				 )
		
		varlabels(months_wgt_avg "\shortstack{Months Open x Disability Pref}"
				  L.months_wgt_avg "\shortstack{Lag Months Open x Disability Pref}"
				  months_open "\shortstack{Months Open}"
				  L.months_open "\shortstack{Lag Months Open}")
		b(%05.4fc)
		se(%05.4fc)
		r2
		replace
		label
		eqlabels(none)
		star(* 0.10 ** 0.05 *** 0.01)
		stats(N  dv_mean , label("N"    "DV Mean" ) fmt(%6.0fc %5.3f))

;
# delimit cr
}




***NEW TABLE: ALL DIS PREF AND INTERACTION
preserve

rename countyfips cntyfips
merge 1:1 cntyfips year using "AUXdata/pha_full_number.dta", gen(pha_num_mrg)
	*Keep if the number of PHA we have data on is within 1 of the HUD data
	keep if num_pha == n_pha_full_file  // benefit of the doubt if were 1 pha off
	keep if num_pha==1
rename cntyfips countyfips
sort countyfips year


***TABLE A.3 Panel A***

*Column 1 -- 
xtset countyfips year
areg apps_per_cap  max_everopen open_disab `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year [aweight=pop],  robust a(countyfips)
eststo t4_col1

*Column 2 -- 
xtset countyfips year
areg apps_per_cap   max_everopen open_disab L.max_everopen L.open_disab `state_cntrl' `cnty_cntrl_noPCT1864' i.ssi_office_code#i.year  i.year  [aweight=pop],   robust a(countyfips)
eststo t4_col2
/

# delimit ;
	esttab  t4_col1 t4_col2  
	using "Output/table4_interact_wStatePHA.tex"
	,
		keep( max_everopen open_disab L.max_everopen L.open_disab)
		b(%05.4fc)
		se(%05.4fc)
		varlabels( max_everopen "\shortstack{Open}"
				  open_disab "\shortstack{Open x Disability Pref}"	
				  L.max_everopen "\shortstack{Lag Open}"
				  L.open_disab "\shortstack{Lag Open x Disability Pref}"			  
				  )
		nonotes
		nonumbers
		r2
		replace
		label
		eqlabels(none)
		star(* 0.10 ** 0.05 *** 0.01)
		stats(N  dv_mean , label("N"   "DV Mean" ) fmt(%6.0fc %5.3f))

;
# delimit cr

restore
